﻿Module ResourceDao

    Public Sub insert(ByVal firstname As String, ByVal surname As String, ByVal documentType As Integer, ByVal document As String, ByVal email As String, ByVal hourCost As Decimal)
        DataAccessConnection.openConnection()
        Dim command As New Data.OleDb.OleDbCommand

        Dim query As String = ""

        query = "INSERT INTO RESOURCES(firstname, surname, document_type, "
        query &= "document_number, email, create_date, hour_cost)"
        query &= " values('" & firstname & "'"
        query &= ", '" & surname & "'"
        query &= ", '" & documentType & "'"
        query &= ", '" & document & "'"
        query &= ", '" & email & "'"
        query &= ", '" & Today.Date & "'"
        query &= ", '" & hourCost & "' )"

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Sub update(ByVal id As Integer, ByVal firstname As String, ByVal surname As String, ByVal documentType As Integer, ByVal document As String, ByVal email As String, ByVal hourCost As Decimal)
        DataAccessConnection.openConnection()
        Dim command As New Data.OleDb.OleDbCommand

        Dim query As String = ""

        query = "UPDATE RESOURCES "
        query &= " SET firstname = '" & firstname & "' , "
        query &= " surname = '" & surname & "' , "
        query &= " document_type = " & documentType & " , "
        query &= " document_number = " & document & " , "
        query &= " email = '" & email & "' , "
        query &= " hour_cost = " & hourCost & ""
        query &= " WHERE id = " & id & ""


        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Function searchDataByFilter(ByVal stringTable As String, ByVal id As Integer, ByVal firstname As String, ByVal surname As String, ByVal document_type As Integer, ByVal document_number As Integer) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New Data.OleDb.OleDbCommand
        Dim table As New Data.DataTable

        Dim query As String = ""

        query = "SELECT * FROM RESOURCES WHERE "
        query &= " firstname like ""%" & firstname & "%"""
        query &= " AND surname like ""%" & surname & "%"""
        query &= " AND (document_type = " & document_type & " OR " & document_type & " = 0)"
        query &= " AND (document_number = " & document_number & " OR " & document_number & " = 0)"
        query &= " AND (id = " & id & " OR " & id & " = 0)"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function
End Module
